#!/bin/bash
APP=online_edu

# 如果输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d "-1 day" +%F`
fi


dws_traffic_user_first_video_play_td="
insert overwrite table ${APP}.dws_traffic_user_first_video_play_td partition (dt = '$do_date')
select nvl(old.user_id, new.user_id),
       if(old.user_id is null, new.play_date_first, old.play_date_first),
       nvl(old.course_id, new.course_id),
       nvl(old.course_name, new.course_name),
       nvl(old.category_id, new.category_id),
       nvl(old.category_name, new.category_name),
       nvl(old.subject_id, new.subject_id),
       nvl(old.subject_name, new.subject_name)
from (
         select user_id,
                play_date_first,
                course_id,
                course_name,
                category_id,
                category_name,
                subject_id,
                subject_name
         from ${APP}.dws_traffic_user_first_video_play_td
         where dt = date_add('$do_date', -1)
     ) old
         full outer join
     (
         select user_id,
                min(date_id) play_date_first,
                t1.course_id,
                t3.course_name,
                t3.category_id,
                t3.category_name,
                t3.subject_id,
                t3.subject_name
         from ${APP}.dwd_traffic_video_play_inc t1
                  left join ${APP}.dim_video_full t2
                            on t1.course_id = t2.course_id
                                and t1.dt = '$do_date'
                                and t2.dt = '$do_date'
                  left join ${APP}.dim_course_subject_category_full t3
                            on t2.course_id = t3.course_id and t3.dt = '$do_date'
         group by user_id, t1.course_id, t3.course_name, t3.category_id, t3.category_name, t3.subject_id, t3.subject_name
     ) new
     on old.user_id = new.user_id and old.course_id = new.course_id;
"

dws_user_user_login_td="
insert overwrite table ${APP}.dws_user_user_login_td partition(dt='$do_date')
select
    nvl(old.user_id,new.user_id),
    if(new.user_id is null,old.login_date_last,'2022-07-21'),
    nvl(old.login_count_td,0)+nvl(new.login_count_1d,0)
from
(
    select
        user_id,
        login_date_last,
        login_count_td
    from ${APP}.dws_user_user_login_td
    where dt=date_add('$do_date',-1)
)old
full outer join
(
    select
        user_id,
        count(*) login_count_1d
    from ${APP}.dwd_user_login_inc
    where dt='$do_date'
    group by user_id
)new
on old.user_id=new.user_id;
"

dws_user_new_user_order_td="
insert overwrite table ${APP}.dws_user_new_user_order_td partition(dt = '$do_date')
select
    user_id,
    order_id,
    data_id,
    create_time,
    course_id,
    origin_amount,
    coupon_reduce,
    final_amount
from(
    select 
        user_id,
        order_id,
        data_id,
        create_time,
        course_id,
        origin_amount,
        coupon_reduce,
        final_amount,
        row_number() over(partition by user_id order by create_time) rn
    from ${APP}.dwd_trade_order_detail_inc
    where dt = date_sub('$do_date',1)
    and user_id not in (
    select 
        user_id
    from ${APP}.dws_user_new_user_order_td
    )
)t2
where rn = 1;
"

dws_user_new_user_payment_td="
insert overwrite table ${APP}.dws_user_new_user_payment_td partition(dt = '$do_date')
select
    user_id,
    order_id,
    data_id,
    callback_time,
    alipay_trade_no,
    total_amount,
    trade_body,
    payment_type
from(
    select 
        user_id,
        order_id,
        data_id,
        callback_time,
        alipay_trade_no,
        total_amount,
        trade_body,
        payment_type,
        row_number() over(partition by user_id order by callback_time) rn
    from ${APP}.dwd_trade_pay_detail_suc_inc
    where dt = date_sub('$do_date',1)
    and user_id not in (
    select 
        user_id
    from ${APP}.dws_user_new_user_payment_td
    )
)t2
where rn = 1;
"

case $1 in 
    "dws_traffic_user_first_video_play_td" )
        hive -e "$dws_traffic_user_first_video_play_td"
        ;;
    "dws_user_user_login_td" )
        hive -e "$dws_user_user_login_td"
        ;;
    "dws_user_new_user_order_td" )
        hive -e "$dws_user_new_user_order_td"
        ;;
    "dws_user_new_user_payment_td" )
        hive -e "$dws_user_new_user_payment_td"
        ;;
    "all" )
        hive -e "$dws_traffic_user_first_video_play_td$dws_user_user_login_td$dws_user_new_user_order_td$dws_user_new_user_payment_td"
        ;;
esac
